-- Script n°011 - Creation of tables "camerabrand" and "cameramodel".
BEGIN;

-- Drop objects if any.
DROP TABLE IF EXISTS cameramodel;
DROP TABLE IF EXISTS camerabrand;

DROP SEQUENCE IF EXISTS seq_cameramodel;
DROP SEQUENCE IF EXISTS seq_camerabrand;

-- Create sequences
CREATE SEQUENCE seq_camerabrand
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

ALTER TABLE seq_camerabrand OWNER TO chelioudbdev;
GRANT ALL ON TABLE seq_camerabrand TO chelioudbdev;
GRANT SELECT, UPDATE ON TABLE seq_camerabrand TO cheliouwebdev;

CREATE SEQUENCE seq_cameramodel
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

ALTER TABLE seq_cameramodel OWNER TO chelioudbdev;
GRANT ALL ON TABLE seq_cameramodel TO chelioudbdev;
GRANT SELECT, UPDATE ON TABLE seq_cameramodel TO cheliouwebdev;

-- Create tables
CREATE TABLE camerabrand (
   id                   NUMERIC              NOT NULL DEFAULT nextval('seq_camerabrand'::regclass),
   name					VARCHAR(100)		 NOT NULL,
   CONSTRAINT pk_camerabrand PRIMARY KEY (id)
)WITH (
  OIDS=FALSE
);

ALTER TABLE camerabrand OWNER TO chelioudbdev;
GRANT ALL ON TABLE camerabrand TO chelioudbdev;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE camerabrand TO cheliouwebdev;

CREATE TABLE cameramodel (
   id                   NUMERIC              NOT NULL DEFAULT nextval('seq_cameramodel'::regclass),
   name					VARCHAR(100)		 NOT NULL,
   camerabrandid        NUMERIC              NOT NULL,
   CONSTRAINT pk_cameramodel PRIMARY KEY (id)
)WITH (
  OIDS=FALSE
);

ALTER TABLE cameramodel
   ADD CONSTRAINT fk_cameramodel_camerabrand FOREIGN KEY (camerabrandid)
      REFERENCES camerabrand (id)
      ON DELETE RESTRICT ON UPDATE RESTRICT;
      
ALTER TABLE cameramodel OWNER TO chelioudbdev;
GRANT ALL ON TABLE cameramodel TO chelioudbdev;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE cameramodel TO cheliouwebdev;

-- Add foreign key to "cameramodel" into the "photo" table.
-- Constraints will be add in the following script.
ALTER TABLE photo ADD COLUMN cameramodelid NUMERIC;

END;